package com.yc.fruitmarket.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
	//建立连接
	public Connection getCon() {
		Connection con = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitmarket?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8","root","a");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	//放两个方法，增删改是返回一个数据，查询是返回另一个数据
	//参数：数据不确定多少个，类型可以是Object
	public int doUpdate(String sql, Object... params) {
		int result = -1;
		//获取连接
		Connection con = getCon();
		//创建预处理语句对象
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			//注入参数
			doParams(ps, params);
			//执行
			result = ps.executeUpdate();
			//关闭资源
			closeAll(con, ps, null);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public int doUpdateBySeq(String sql, String cname, Object... params) {
		int result = -1;
		//获取连接
		Connection con = getCon();
		//创建预处理语句对象
		try {
			String[] cnames = {cname};//设置，那一列是序列自增的列
			PreparedStatement ps = con.prepareStatement(sql, cnames);
			//注入参数
			doParams(ps, params);
			//执行
			result = ps.executeUpdate();
			if(result > 0) {
				//查序列值
				ResultSet rs = ps.getGeneratedKeys();
				if(rs.next()) {
					result = rs.getInt(1);
				}
			}
			//关闭资源
			closeAll(con, ps, null);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	//注入参数
	public void doParams(PreparedStatement ps, Object... params ) {
		if(ps != null && params.length > 0) {
			//循环注入
			for(int i = 0; i < params.length; i++){
				try {
					ps.setObject(i + 1, params[i]);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	//查询	一个字段要对应一个键Map
	public List<Map<String, String>> find(String sql, Object... params ) {
		List<Map<String, String>> list = new ArrayList<>();
		try {
			Connection con = getCon();
			PreparedStatement ps = con.prepareStatement(sql);
			doParams(ps, params);
			ResultSet rs = ps.executeQuery();
			
			//不一样的地方
			//如何得到这个表里面的字段
			ResultSetMetaData rsmd = rs.getMetaData();
			String[] cnames = new String[rsmd.getColumnCount()];
			for(int i = 0; i < cnames.length; i++) {
				cnames[i] = rsmd.getColumnName(i + 1);
			}
			//字段有了，开始取值
			while(rs.next()) {
				Map<String,String> map = new HashMap<>();
				for(int i = 0; i < cnames.length; i++) {
					String key = cnames[i];
					String value = rs.getString(key);
					//为了方便取值，转换为小写
					map.put(key.toLowerCase(), value);
				}
				//一条数据完毕，添加到list中
				list.add(map);
			}
			closeAll(con, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	//读取包含blob/clob的数据
	public List<Map<String, Object>> findByBlob(String sql, Object... params ) {
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			Connection con = getCon();
			PreparedStatement ps = con.prepareStatement(sql);
			doParams(ps, params);
			ResultSet rs = ps.executeQuery();
			
			//不一样的地方
			//如何得到这个表里面的字段
			ResultSetMetaData rsmd = rs.getMetaData();
			String[] cnames = new String[rsmd.getColumnCount()];
			for(int i = 0; i < cnames.length; i++) {
				cnames[i] = rsmd.getColumnName(i + 1);
			}
			//字段有了，开始取值
			while(rs.next()) {
				Map<String,Object> map = new HashMap<>();
				for(int i = 0; i < cnames.length; i++) {
					String key = cnames[i];
					//String value = rs.getString(key);
					Object obj = null;
					//这里得区分
					if("oracle.sql.BLOB".equals(rs.getObject(key).getClass().getName())) {
						obj = rs.getBytes(key);
					}else {
						obj = rs.getString(key);
					}
					//为了方便取值，转换为小写
					map.put(key.toLowerCase(), obj);
				}
				//一条数据完毕，添加到list中
				list.add(map);
			}
			closeAll(con, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	//关闭资源
	public void closeAll(Connection con, PreparedStatement ps, ResultSet rs) {
		try {
			if(rs != null) {
				rs.close();
			}
			if(ps != null) {
				ps.close();
			}
			if(con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//加载驱动
	static {
		try {	//5.5以上com.mysql.cj.jdbc.Driver
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}
